import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_FESJob import Predict_FESJob
class Predict_6627_2Job(AbstractDPJob):
    def __init__(self,
                 ):
        super(Predict_6627_2Job, self).__init__()

        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6627_2Job, self).do_execute()
        import pandas as pd
        import numpy as np
        from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, \
            mean_absolute_error
        from sklearn.preprocessing import StandardScaler, MinMaxScaler
        from scipy.spatial import distance
        from sklearn.tree import DecisionTreeRegressor
        from sklearn.linear_model import LinearRegression, SGDRegressor
        from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, \
            BaggingRegressor, ExtraTreesRegressor
        import sklearn.model_selection as ms
        from sklearn.neighbors import KNeighborsRegressor
        import xgboost as xgb
        from sklearn.linear_model import Lasso, Ridge, ElasticNet
        import datetime
        from sqlalchemy import create_engine
        from sqlalchemy.pool import NullPool
        column_aim_1 = 'IW'
        column_aim_2 = '14'
        column_aim_3 = '24'
        column_aim_4 = '34'
        column_aim_5 = '54'
        xlsx_name = 'D:/repos/sicost/clean3_2.xlsx'
        data_1 = pd.read_excel(xlsx_name)
        data_1.columns = data_1.columns.str.upper()
        data_1['PROD_DATE'] = data_1['PROD_DATE'].astype(str)
        xlsx_name = 'D:/repos/sicost/clean3_1.xlsx'
        data_0 = pd.read_excel(xlsx_name)
        data_0.columns = data_0.columns.str.upper()
        data_0['PROD_DATE'] = data_0['PROD_DATE'].astype(str)
        data_2 = data_0[data_0['PRET_S_AIM'] == int(column_aim_2)]
        data_2 = data_2.reset_index(drop=True)
        data_3 = data_0[data_0['PRET_S_AIM'] == int(column_aim_3)]
        data_3 = data_3.reset_index(drop=True)
        data_4 = data_0[data_0['PRET_S_AIM'] == int(column_aim_4)]
        data_4 = data_4.reset_index(drop=True)
        data_5 = data_0[data_0['PRET_S_AIM'] == int(column_aim_5)]
        data_5 = data_5.reset_index(drop=True)
        data_all = pd.concat([data_1, data_2])
        data_all = pd.concat([data_all, data_3])
        data_all = pd.concat([data_all, data_4])
        data_all = pd.concat([data_all, data_5])

        sql_condition1 = " AND left(ST_NO,2) = 'IW' "
        sql_condition2 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 14"
        sql_condition3 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 24"
        sql_condition4 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 34"
        sql_condition5 = " AND left(ST_NO,2) != 'IW' AND PRET_S_AIM = 54"

        def clean_data(sql_condition):
            start = datetime.datetime.now()
            delta_day2 = 10
            delta_day1 = delta_day2 + 365
            p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
            p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
            sql = " select *  " \
                  " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
                  " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
                  " AND PROD_DATE<'%s' " \
                  " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
            print(sql)
            df0 = pd.read_sql_query(sql, con=db_conn_mpp)
            col_list = []
            col_list.append('PROD_DATE')
            col_list.append('ST_NO')
            col_list.append('AIM_ST_S')
            col_list.append('ACT_ST_S')
            col_list.append('PRET_S_AIM')
            col_list.append('REVISE_PRET_S')
            col_list.append('IRON_WT_TOTAL')
            # col_list.append('IRON_WT_NET')
            col_list.append('STTEMP_AFIRON')
            col_list.append('RECV_SI')
            col_list.append('RECV_S')
            col_list.append('RECV_TI')
            # col_list.append('AFTTEMP_RON1')
            col_list.append('AFTEMP_AFIRON')
            col_list.append('CAO_LOSS_SUM')
            col_list.append('CAO_DH')
            col_list.append('DES_WHISK_DEPTH1')
            print(col_list)

            df0.columns = df0.columns.str.upper()
            df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)
            df = df0[col_list]
            df3 = df
            df3['DELTA'] = abs(df3['AIM_ST_S'] - df3['ACT_ST_S'])
            # 数据清洗1
            df3_clean1 = df3[(df3['DELTA'] <= 100)]
            df3_clean1.drop(['ACT_ST_S'], axis=1, inplace=True)
            df3_clean1.drop(['DELTA'], axis=1, inplace=True)

            # 数据清洗2
            def clean_data(df, gamma):
                column_name_list = df.columns.tolist()
                column_name_list.remove('PROD_DATE')
                column_name_list.remove('ST_NO')
                column_name_list.remove('AIM_ST_S')
                column_name_num = len(column_name_list)
                clean_str_start = 'df_new = df['
                clean_str_end = ']'
                ldict1 = {}
                for i in range(0, column_name_num):
                    # print(i)
                    # print(column_name_list[i])
                    column_name_tmp = column_name_list[i]
                    exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                    clean_str3 = ldict1["clean_str3"]
                    if i == 0:
                        clean_str_start = clean_str_start + clean_str3
                    else:
                        clean_str_start = clean_str_start + ' & ' + clean_str3
                clean_str = clean_str_start + clean_str_end
                # print(clean_str)
                exec(clean_str, locals(), ldict1)
                df_new = ldict1["df_new"]
                df_new = df_new.reset_index(drop=True)
                return df_new

            gamma = 1.5
            df3_clean2 = clean_data(df3_clean1, gamma)
            df3_clean2['DH'] = df3_clean2['CAO_LOSS_SUM'] / df3_clean2['IRON_WT_TOTAL'] * 100
            df3_clean2['TEMP'] = df3_clean2['STTEMP_AFIRON'] - df3_clean2['AFTEMP_AFIRON']
            # 数据清洗3
            df3_clean3 = df3_clean2[(df3_clean2['TEMP'] > 0)]
            df3_clean3 = df3_clean3.reset_index(drop=True)
            df3_clean3.drop(['STTEMP_AFIRON'], axis=1, inplace=True)
            df3_clean3.drop(['AFTEMP_AFIRON'], axis=1, inplace=True)
            return df3_clean3

        def average(num1, num2):
            return (num1 + num2) / 2

        def cal_dh_model(data, column_aim_str):
            df4 = data
            if column_aim_str == 'IW':
                column_aim_tmp = 'IW'
            else:
                mode_fret_s_aim = int(column_aim_str)
                column_aim_tmp = 'AIM_' + str(int(mode_fret_s_aim))
            recv_s_max_list1 = [120, 140, 160, 180, 200, 300, 400, 500]
            recv_s_max_list2 = [0, 200, 300, 400, 500]
            gamma = 1.5
            q1 = df4['RECV_S'].quantile(0.25)
            q3 = df4['RECV_S'].quantile(0.75)
            iqr_val = q3 - q1
            q1_2 = df4['DH'].quantile(0.25)
            q3_2 = df4['DH'].quantile(0.75)
            iqr_val_2 = q3_2 - q1_2
            q1_3 = df4['DES_WHISK_DEPTH1'].quantile(0.25)
            q3_3 = df4['DES_WHISK_DEPTH1'].quantile(0.75)
            iqr_val_3 = q3_3 - q1_3
            df5 = df4[(df4['RECV_S'] <= q3 + gamma * iqr_val) & (df4['RECV_S'] >= q1 - gamma * iqr_val)
                      & (df4['DH'] <= q3_2 + gamma * iqr_val_2) & (df4['DH'] >= q1_2 - gamma * iqr_val_2)
                      & (df4['DES_WHISK_DEPTH1'] <= q3_3 + gamma * iqr_val_3) & (
                                  df4['DES_WHISK_DEPTH1'] >= q1_3 - gamma * iqr_val_3)]
            df5 = df5.reset_index(drop=True)
            # 单耗
            df_out1 = pd.DataFrame(
                columns=['RECV_S_MAX', 'dh1', 'dh2'])
            dict = {}
            for i in range(0, len(recv_s_max_list1)):
                print(recv_s_max_list1[i])
                if i == 0:
                    interval = recv_s_max_list1[i + 1] - recv_s_max_list1[i]
                else:
                    interval = recv_s_max_list1[i] - recv_s_max_list1[i - 1]
                min_tmp = recv_s_max_list1[i] - interval / 10
                max_tmp = recv_s_max_list1[i] + interval / 10
                df5_copy = df5.copy()
                df5_copy['DELTA_RECV_S'] = abs(df5_copy['RECV_S'] - recv_s_max_list1[i])
                df5_copy_sorted = df5_copy.sort_values(by='DELTA_RECV_S')
                # , ascending = False
                df5_copy_sorted = df5_copy_sorted.reset_index(drop=True)
                df5_tmp2 = df5_copy_sorted.head(10)
                df5_tmp1 = df5_copy[(df5_copy['RECV_S'] <= max_tmp) & (df5_copy['RECV_S'] >= min_tmp)]
                df5_tmp3 = df5_copy[(df5_copy['RECV_S'] == recv_s_max_list1[i])]
                row_count1 = len(df5_tmp1)
                row_count3 = len(df5_tmp3)
                if row_count3 > 0:
                    y1_pred = df5_tmp3['DH'].mean()
                    print(y1_pred)
                else:
                    y1_pred = 0
                dict['RECV_S_MAX'] = recv_s_max_list1[i]
                dict['dh1'] = y1_pred
                if row_count1 <= 5:
                    df5_tmp = df5_tmp2
                else:
                    df5_tmp = df5_tmp1
                model = LinearRegression()
                X = df5_tmp['RECV_S'].values.reshape(-1, 1)
                y = df5_tmp['DH'].values
                model.fit(X, y)
                list_tmp = []
                list_tmp.append(recv_s_max_list1[i])
                array_tmp = np.array(list_tmp).reshape(-1, 1)
                y_pred = model.predict(array_tmp)
                # print(y_pred)
                y1_pred = y_pred[0]
                print(y1_pred)
                dict['dh2'] = y1_pred
                new_row = pd.Series(dict)
                df_out1 = df_out1.append(new_row, ignore_index=True)
            df_out1_copy = df_out1.copy()
            df_out1_copy = df_out1_copy[df_out1_copy['dh1'] != 0]
            df_out1_copy = df_out1_copy.reset_index(drop=True)
            model = LinearRegression()
            X = df_out1_copy['RECV_S_MAX'].values.reshape(-1, 1)
            y = df_out1_copy['dh1'].values
            model.fit(X, y)
            for index, row in df_out1.iterrows():
                if row['dh1'] == 0:
                    x_pred = row['RECV_S_MAX']
                    list_tmp = []
                    list_tmp.append(x_pred)
                    array_tmp = np.array(list_tmp).reshape(-1, 1)
                    y_pred = model.predict(array_tmp)
                    # print(y_pred)
                    y1_pred = y_pred[0]
                    row['dh1'] = y1_pred
            print(df_out1)
            values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[0], 'dh1']
            dh1_min = float(values)
            values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[0], 'dh2']
            dh2_min = float(values)
            values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[-1], 'dh1']
            dh1_max = float(values)
            values = df_out1.loc[df_out1['RECV_S_MAX'] == recv_s_max_list1[-1], 'dh2']
            dh2_max = float(values)
            dh_min = min(dh1_min, dh2_min)
            dh_max = max(dh1_max, dh2_max)
            df_out1['dh3'] = df_out1['dh2']
            df_out1['dh4'] = df_out1['dh2']
            for index, row in df_out1.iterrows():
                if index == 0:
                    row['dh3'] = dh_min
                    row['dh4'] = dh_min
                elif index == len(recv_s_max_list1) - 1:
                    row['dh3'] = dh_max
                    row['dh4'] = dh_max
                else:
                    dh3_tmp = dh_min + (dh_max - dh_min) / (recv_s_max_list1[-1] - recv_s_max_list1[0]) * (
                                row['RECV_S_MAX'] - recv_s_max_list1[0])
                    row['dh3'] = dh3_tmp
            df_out1['dh4_next'] = df_out1['dh4'].shift(-1)
            df_out1.dh4_next.fillna(99999, inplace=True)
            df_out1['dh4_pred'] = df_out1['dh4'].shift(1)
            df_out1.dh4_pred.fillna(0, inplace=True)
            change_value = 0
            df_out1['dh_tmp'] = df_out1['dh4']
            for index, row in df_out1.iterrows():
                if index != 0 and index != len(recv_s_max_list1) - 1:
                    # 检验
                    print(row['dh4_pred'])
                    print(change_value)
                    if row['dh4'] > change_value and row['dh4'] < row['dh4_next']:
                        change_value = row['dh4']
                    else:
                        print('需要改变')
                        new_value1 = average(row['dh3'], row['dh4'])
                        if new_value1 > change_value and new_value1 < row['dh4_next']:
                            change_value = new_value1
                        else:
                            new_value2 = average(row['dh3'], new_value1)
                            if new_value2 > change_value and new_value2 < row['dh4_next']:
                                change_value = new_value2
                            else:
                                change_value = row['dh3']
                else:
                    change_value = row['dh4']
                row['dh_tmp'] = change_value
            df_out1 = df_out1[['RECV_S_MAX', 'dh_tmp']]
            df_out1.rename(columns={'dh_tmp': column_aim_tmp}, inplace=True)
            return df_out1

        data_out_1 = cal_dh_model(data_1, column_aim_1)
        data_out_2 = cal_dh_model(data_2, column_aim_2)
        data_out_3 = cal_dh_model(data_3, column_aim_3)
        data_out_4 = cal_dh_model(data_4, column_aim_4)
        data_out_5 = cal_dh_model(data_5, column_aim_5)
        v = ['RECV_S_MAX']
        data_out_0 = pd.merge(data_out_1, data_out_2, on=v, how='left')
        data_out_0 = pd.merge(data_out_0, data_out_3, on=v, how='left')
        data_out_0 = pd.merge(data_out_0, data_out_4, on=v, how='left')
        data_out_0 = pd.merge(data_out_0, data_out_5, on=v, how='left')
        print(data_out_0)

        df6 = data_all

        recv_s_max_list2 = [0, 200, 300, 400, 500]
        gamma = 1.5
        q1 = df6['RECV_S'].quantile(0.25)
        q3 = df6['RECV_S'].quantile(0.75)
        iqr_val = q3 - q1
        q1_2 = df6['DH'].quantile(0.25)
        q3_2 = df6['DH'].quantile(0.75)
        iqr_val_2 = q3_2 - q1_2
        q1_3 = df6['DES_WHISK_DEPTH1'].quantile(0.25)
        q3_3 = df6['DES_WHISK_DEPTH1'].quantile(0.75)
        iqr_val_3 = q3_3 - q1_3
        df7 = df6[(df6['RECV_S'] <= q3 + gamma * iqr_val) & (df6['RECV_S'] >= q1 - gamma * iqr_val)
                  & (df6['DH'] <= q3_2 + gamma * iqr_val_2) & (df6['DH'] >= q1_2 - gamma * iqr_val_2)
                  & (df6['DES_WHISK_DEPTH1'] <= q3_3 + gamma * iqr_val_3) & (
                              df6['DES_WHISK_DEPTH1'] >= q1_3 - gamma * iqr_val_3)]
        df7 = df7.reset_index(drop=True)
        # 时间
        df_out2 = pd.DataFrame(
            columns=['RECV_S_MAX', 't1', 't2'])
        dict = {}
        for i in range(0, len(recv_s_max_list2)):
            print(recv_s_max_list2[i])
            if i == 0:
                interval = recv_s_max_list2[i + 1] - recv_s_max_list2[i]
            else:
                interval = recv_s_max_list2[i] - recv_s_max_list2[i - 1]
            min_tmp = recv_s_max_list2[i] - interval / 10
            max_tmp = recv_s_max_list2[i] + interval / 10
            df7_copy = df7.copy()
            df7_copy['DELTA_RECV_S'] = abs(df7_copy['RECV_S'] - recv_s_max_list2[i])
            df7_copy_sorted = df7_copy.sort_values(by='DELTA_RECV_S')
            # , ascending = False
            df7_copy_sorted = df7_copy_sorted.reset_index(drop=True)
            df7_tmp2 = df7_copy_sorted.head(10)
            df7_tmp1 = df7_copy[(df7_copy['RECV_S'] <= max_tmp) & (df7_copy['RECV_S'] >= min_tmp)]
            df7_tmp3 = df7_copy[(df7_copy['RECV_S'] == recv_s_max_list2[i])]
            row_count1 = len(df7_tmp1)
            row_count3 = len(df7_tmp3)
            if row_count3 > 0:
                y1_pred = df7_tmp3['DES_WHISK_DEPTH1'].mean()
                print(y1_pred)
            else:
                y1_pred = 0
            dict['RECV_S_MAX'] = recv_s_max_list2[i]
            dict['t1'] = y1_pred
            if row_count1 <= 5:
                df7_tmp = df7_tmp2
            else:
                df7_tmp = df7_tmp1
            model = LinearRegression()
            X = df7_tmp['RECV_S'].values.reshape(-1, 1)
            y = df7_tmp['DES_WHISK_DEPTH1'].values
            model.fit(X, y)
            list_tmp = []
            list_tmp.append(recv_s_max_list2[i])
            array_tmp = np.array(list_tmp).reshape(-1, 1)
            y_pred = model.predict(array_tmp)
            # print(y_pred)
            y1_pred = y_pred[0]
            print(y1_pred)
            dict['t2'] = y1_pred
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
        df_out2_copy = df_out2.copy()
        df_out2_copy = df_out2_copy[df_out2_copy['t1'] != 0]
        df_out2_copy = df_out2_copy.reset_index(drop=True)
        model = LinearRegression()
        X = df_out2_copy['RECV_S_MAX'].values.reshape(-1, 1)
        y = df_out2_copy['t1'].values
        model.fit(X, y)
        for index, row in df_out2.iterrows():
            if row['t1'] == 0:
                x_pred = row['RECV_S_MAX']
                list_tmp = []
                list_tmp.append(x_pred)
                array_tmp = np.array(list_tmp).reshape(-1, 1)
                y_pred = model.predict(array_tmp)
                # print(y_pred)
                y1_pred = y_pred[0]
                row['t1'] = y1_pred
        print(df_out2)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't1']
        t1_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't2']
        t2_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't1']
        t1_max = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't2']
        t2_max = float(values)
        t_min = min(t1_min, t2_min)
        t_max = max(t1_max, t2_max)
        df_out2['t3'] = df_out2['t2']
        df_out2['t4'] = df_out2['t2']
        for index, row in df_out2.iterrows():
            if index == 0:
                row['t3'] = t_min
                row['t4'] = t_min
            elif index == len(recv_s_max_list2) - 1:
                row['t3'] = t_max
                row['t4'] = t_max
            else:
                t3_tmp = t_min + (t_max - t_min) / (recv_s_max_list2[-1] - recv_s_max_list2[0]) * (
                            row['RECV_S_MAX'] - recv_s_max_list2[0])
                row['t3'] = t3_tmp
        df_out2['t4_next'] = df_out2['t4'].shift(-1)
        df_out2.t4_next.fillna(99999, inplace=True)
        df_out2['t4_pred'] = df_out2['t4'].shift(1)
        df_out2.t4_pred.fillna(0, inplace=True)
        change_value = 0
        df_out2['t_tmp'] = df_out2['t4']
        for index, row in df_out2.iterrows():
            if index != 0 and index != len(recv_s_max_list2) - 1:
                # 检验
                print(row['t4_pred'])
                print(change_value)
                if row['t4'] > change_value and row['t4'] < row['t4_next']:
                    change_value = row['t4']
                else:
                    print('需要改变')
                    new_value1 = average(row['t3'], row['t4'])
                    if new_value1 > change_value and new_value1 < row['t4_next']:
                        change_value = new_value1
                    else:
                        new_value2 = average(row['t3'], new_value1)
                        if new_value2 > change_value and new_value2 < row['t4_next']:
                            change_value = new_value2
                        else:
                            change_value = row['t3']
            else:
                change_value = row['t4']
            row['t_tmp'] = change_value
        df_out2 = df_out2[['RECV_S_MAX', 't_tmp']]
        df_out2.rename(columns={'t_tmp': 'WHISK_TIME'}, inplace=True)
        print(df_out2)
        print('finish')

        return data_out_0, df_out2